/*
Navicat MySQL Data Transfer

Source Server         : my
Source Server Version : 50610
Source Host           : localhost:3306
Source Database       : xingcheng

Target Server Type    : MYSQL
Target Server Version : 50610
File Encoding         : 65001

Date: 2017-09-04 12:20:17
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `examresult`
-- ----------------------------
DROP TABLE IF EXISTS `examresult`;
-- 创建表（考试成绩）
CREATE TABLE `examresult` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '考试序号',
  `exam_date` DATE DEFAULT NULL COMMENT '考试时间',
  `score` FLOAT(6,2) NOT NULL COMMENT '考试成绩',
  `st_id` INT(10) UNSIGNED DEFAULT NULL COMMENT '考试学生',
  `sb_id` INT(10) UNSIGNED DEFAULT NULL COMMENT '考试的科目',
  PRIMARY KEY (`id`),
  KEY `fk_stu` (`st_id`),
  KEY `fk_subject` (`sb_id`),
  CONSTRAINT `fk_stu` FOREIGN KEY (`st_id`) REFERENCES `student` (`stuno`),
  CONSTRAINT `fk_subject` FOREIGN KEY (`sb_id`) REFERENCES `subject` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of examresult
-- ----------------------------
--  给考试成绩表中添加数据
INSERT INTO examresult VALUES ('1', '2017-09-01', '90.00', '1', '3');

-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
-- 创建表（年级）
CREATE TABLE `grade` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gname` VARCHAR(100) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gname` (`gname`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
-- 给表（年级）中添加数据
INSERT INTO grade VALUES ('5', 'Java');
INSERT INTO grade VALUES ('1', 'S1');
INSERT INTO grade VALUES ('2', 'S2');
INSERT INTO grade VALUES ('3', 'Y2');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
-- 创建表（学生）
CREATE TABLE `student` (
  `stuno` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `sname` VARCHAR(40) NOT NULL COMMENT '姓名',
  `sex` ENUM('男','女','妖','人妖') DEFAULT '男' COMMENT '性别',
  `telephone` VARCHAR(15) DEFAULT NULL COMMENT '电话',
  `qq` VARCHAR(20) DEFAULT NULL COMMENT 'QQ号',
  `wechat` VARCHAR(50) DEFAULT NULL COMMENT '微信',
  `gradeid` INT(20) UNSIGNED NOT NULL COMMENT '年级',
  `enterdate` DATE DEFAULT NULL COMMENT '入学日期',
  `fromcity` VARCHAR(100) DEFAULT NULL COMMENT '从那个城市来的',
  `birthday` DATE DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(400) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`stuno`),
  KEY `fk_grade2` (`gradeid`),
  CONSTRAINT `fk_grade2` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ---------------------------- 
-- 给学生表添加数据
INSERT INTO student VALUES ('1', '王武', '男', '131800433221', '14574934', NULL, '1', NULL, '阜阳', NULL, '阜阳');
INSERT INTO student VALUES ('2', '王莽', '男', '131800433221', NULL, NULL, '2', '2017-03-10', '合肥', NULL, NULL);
INSERT INTO student VALUES ('3', '李星', '女', '131800433221', '1949944', NULL, '3', '2017-04-10', '合肥', NULL, '阜阳');
INSERT INTO student VALUES ('5', '李八', '女', '131800433221', '666666', NULL, '3', '2017-04-20', '六安', NULL, '阜阳颖州区');
INSERT INTO student VALUES ('6', '牛三', '妖', '13590812397', '14574934', '14574934', '2', '2017-04-21', '六安', NULL, NULL);
INSERT INTO student VALUES ('7', '丁玉锁', '男', NULL, NULL, NULL, '5', NULL, '阜阳', '1995-05-01', NULL);
INSERT INTO student VALUES ('8', '丁钢锁', '男', NULL, NULL, NULL, '5', NULL, '阜阳', '1997-05-01', NULL);

-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
 -- 创建表（科目）
CREATE TABLE `subject` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '科目编号',
  `subjectname` VARCHAR(100) NOT NULL COMMENT '科目名称',
  `classhour` INT(10) UNSIGNED DEFAULT NULL COMMENT '学时',
  `gradeid` INT(10) UNSIGNED NOT NULL COMMENT '年级编号',
  PRIMARY KEY (`id`),
  KEY `fk_grade` (`gradeid`),
  CONSTRAINT `fk_grade` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
 -- 给科目表添加数据
INSERT INTO `subject` VALUES ('1', 'java 基础', '16', '5');
INSERT INTO `subject` VALUES ('3', 'java oop', '20', '2');
INSERT INTO `subject` VALUES ('4', 'java高级特性', '8', '5');
INSERT INTO `subject` VALUES ('6', '利用html+css设计网页', '19', '3');
INSERT INTO `subject` VALUES ('14', '使用jquery实现网页特效', '12', '5');
INSERT INTO `subject` VALUES ('15', '数据库编程', '12', '5');
INSERT INTO `subject` VALUES ('16', 'java web编程', '16', '5');
INSERT INTO `subject` VALUES ('17', '深入mybatis', '10', '5');
INSERT INTO `subject` VALUES ('18', 'spring详解', '8', '5');
INSERT INTO `subject` VALUES ('19', 'struts2初步', '3', '5');
INSERT INTO `subject` VALUES ('20', 'hibernate浅窥', '2', '5');

-- 给每个表添加三个数据

INSERT INTO examresult 
VALUES ('2', '2017-09-01', '90.00', '1', '3'),
	('3', '2017-09-01', '90.00', '1', '3'),
	('4', '2017-09-01', '90.00', '1', '3');
	

INSERT INTO grade 
VALUES ('4', '语文'),('6', '数学'),('7', '物理');

INSERT INTO student 
VALUES ('9', '关羽', '男', '156585685552', '8755785', NULL, '4', NULL, '四川', NULL, '成都'),
	('10', '孙尚香', '女', '4552572572', '54275', NULL, '5', NULL, '苏州', NULL, '杭州'),
	('11', '周瑜', '男', '785785272', '8542', NULL, '6', NULL, '芜湖', NULL, '合肥');


INSERT INTO `subject` 
VALUES ('2', '小学语文', '56', '4'),
	('5', '中学数学', '52', '6'),
	('7', '高中物理', '65', '7');

-- 找出不是六安的男学生信息

SELECT * FROM student
WHERE sex = '男' AND fromcity !='六安';


-- 每月最后一天出生的学生信息 方法1：
SELECT * FROM student 
WHERE DAYOFMONTH (DATE_ADD(birthday, INTERVAL 1 DAY) ) = 1;
-- 每月最后一天出生的学生信息 方法2：
SELECT * FROM student 
WHERE birthday =  LAST_DAY(birthday);

-- 找出课程号是3 的课程 ，考了几次  平均分是多少

SELECT COUNT(`id`) AS 考试次数,AVG(`score`) AS 平均分 FROM `examresult` 
WHERE `sb_id` = 3;

-- 查询2016年2月17日考试前五名的学员和分数

SELECT st_id AS 学号,score as成绩 FROM examresult WHERE exam_date = '2016-02-17' LIMIT 5;

-- 查询所有女学生

SELECT sname AS 姓名, birthday AS 出生日期, telephone AS 手机号 FROM student 
WHERE sex = '女'  ORDER BY birthday ASC LIMIT 1,5;
-- 参加2016年2月17日考试的所有学员的最高分、最低分、平均分


SELECT COUNT(*) ,MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分 FROM examresult WHERE exam_date = '2016-02-17'; 

 -- 此方法不全对  存在遗漏（存在生日一样的）
SELECT * FROM student WHERE birthday = (
SELECT birthday FROM student ORDER BY birthday ASC LIMIT 1 
);

--  全对的方法
SELECT * FROM student WHERE birthday = (
SELECT MAX(birthday)  FROM student  
);

SELECT * FROM student WHERE stuno IN(
SELECT st_id FROM examresult WHERE sb_id IN(
SELECT id FROM `subject` WHERE subjectname = 'java 基础'
)
)

SELECT subjectname FROM `subject` WHERE gradeid IN(
SELECT id FROM grade WHERE gname = 'S1'
)

-- 3：找出所有80后的学生，即1980-1989之间出生的学生
SELECT * FROM student WHERE YEAR(birthday)<=1989 AND YEAR(birthday)>=1980;



-- 4:找出来自 合肥 地区，有考试低于60的学生信息

SELECT * FROM student WHERE stuno IN (
SELECT st_id FROM examresult WHERE score <60
) AND fromcity = '合肥';

--   2017年9月7日
-- 查询有S2 的学生就查询参加S2 考试的学员信息和科目信息

SELECT * FROM examresult WHERE EXISTS (
SELECT * FROM student WHERE gradeid IN(
SELECT id FROM grade WHERE gname = 'S2'
)
)AND sb_id IN (
SELECT id FROM `subject` WHERE gradeid IN (
SELECT id FROM grade WHERE gname = 'S2'
)
)

-- 统计各个性别有多少人，最大的生日，最小的生日

SELECT sex AS 性别,MIN( birthday) AS 最小的生日 ,MAX( birthday) AS 最大的生日, COUNT(*) AS 人数 FROM student
WHERE stuno IN (
SELECT st_id FROM examresult WHERE sb_id IN(
SELECT id FROM grade WHERE gname = 'S2'
)
)
 GROUP BY sex;

-- 统计各个地区 各种性别的人数
SELECT fromcity AS 地区,sex AS 性别 ,COUNT(*) AS 人数 FROM student 
GROUP BY fromcity ,sex;

-- 去掉重复

SELECT DISTINCT fromcity FROM student ;

--  查询各个地区的人数，最后至查询超过两个人的地区和人数
--  方法 1 
SELECT fromcity AS 地区, COUNT( *) AS 人数 FROM student 
GROUP BY fromcity 
 HAVING COUNT( *)>=2;
--  方法 2 
SELECT 地区, 人数 FROM (
SELECT fromcity AS 地区, COUNT( *) AS 人数 FROM student 
GROUP BY fromcity 
) aa 
WHERE aa.人数>=2;


--  将查询的两个结果合在一起  （ UNION）
SELECT id AS 课程号 , subjectname AS  课程名 FROM `subject` 
UNION
SELECT stuno AS 学号, sname AS 学生姓名  FROM student 
;

-- 列出学生和考试的成绩(没有参加考试的同学也显示出来)
--  左外连接（ 根据左表进行匹配，没有匹配到的显示null）
SELECT s.`sname` AS 学生姓名, e.`score` AS 考试成绩 FROM student s 
LEFT JOIN examresult e 
ON (s.`stuno` = e.`st_id` )

-- 内连接( 只显示能够匹配到的)
SELECT s.`sname` AS 学生姓名, e.`score` AS 考试成绩 FROM student s 
INNER JOIN examresult e 
ON (s.`stuno` = e.`st_id` )

-- 
SELECT s.`sname` AS 学生姓名, e.`score` AS 考试成绩 , su.`subjectname` AS 科目 FROM student s 
INNER JOIN examresult e ON s.`stuno` = e.`st_id` 
INNER JOIN`subject` su ON su.`id` = e.`sb_id`

--  左连接显示年级和对应的科目（显示全部年纪）
SELECT g.gname AS 年级名称, su.subjectname AS k科目名称, su.classhour AS 学时 FROM grade  g
LEFT JOIN `subject` su
ON( g.id = su.gradeid )

--  2017 年9 月7 日 课后作业

-- 1:查询累计考试次数（不同科目） 超过3次的学生信息（全部）。
SELECT * FROM student WHERE stuno IN(
SELECT st_id FROM examresult 
GROUP BY st_id
HAVING COUNT(DISTINCT sb_id)>3
)

 -- 2：查询有过补考的学生信息（同一门课超过2次，认为是补考）
 
 
SELECT * FROM student WHERE stuno IN(
SELECT st_id FROM examresult 
GROUP BY st_id
HAVING COUNT(sb_id)>2
)

-- 3：最近半年内入学的学生

SELECT * FROM student WHERE enterdate >=DATE_ADD(NOW(), INTERVAL -6 MONTH)


-- 4: 查询年级信息，以及改年级的课程数，没有课程的也要显示，对应的课程数目为0

-- 方法  1： 使用伪类

SELECT aa.年级名称,COUNT(课程名) AS 课程数目 FROM(
SELECT g.gname AS 年级名称, s.subjectname AS 课程名 FROM grade g 
LEFT JOIN `subject` s 
ON( g.id = s.`gradeid`)
) aa 
GROUP BY aa.年级名称

-- 方法   2：没有使用伪类

SELECT g.gname AS 年级名称,COUNT( s.subjectname) AS 课程数 FROM grade g 
LEFT JOIN `subject` s 
ON( g.id = s.`gradeid`)
GROUP BY g.gname
-- 2017 - 09- 09
-- 1,内连接的另一种写法

SELECT g.id,s.subjectname,s.classhour,g.gname FROM `subject` s,grade g
WHERE s.gradeid = g.id;


-- 制作成绩单

SELECT gr.`gname` AS 年级, st.`sname` AS 学生姓名, su.`subjectname` AS 课程名称, ex.`exam_date` AS 考试日期 , ex.`score` AS 考试成绩 
FROM  examresult ex
RIGHT JOIN `subject` su ON ex.`sb_id` = su.`id`
RIGHT JOIN  grade gr ON gr.id = su.`gradeid`
RIGHT JOIN  student st ON st.`stuno` = ex.`st_id`


-- 4: 查询年级信息，以及改年级的课程数，没有课程的也要显示，对应的课程数目为0
-- 方法：将子查询作为结果集
SELECT  g.* , (SELECT COUNT(*) FROM `subject` s WHERE s.gradeid = g.id ) AS 课程数
 FROM grade g


-- 关键字 LIKE（只能查字符串  不支持数字查询）

SELECT * FROM student WHERE telephone LIKE '1[3-5]%'  ( 不行，不支持 )

-- 正确的方法
SELECT * FROM student WHERE telephone REGEXP '^1[3-5]' 

--  找出各个年级最小的学生  只要年级编号和年龄

SELECT gradeid AS 年级编号, MAX(birthday) AS 生日,stuno AS 编号 FROM student 
GROUP BY gradeid;

--   找出各个年级最小的学生  显示全部信息
-- 错误方法：学生编号没有与实际对应上，而是选取每组的第一个数据的学生编号

SELECT * FROM student WHERE stuno IN (
SELECT stuno FROM (
SELECT gradeid AS 年级编号, MAX(birthday) AS 生日,stuno  FROM student 
GROUP BY gradeid
) tt
)

-- 方法1 ： 使用伪类
SELECT s.* FROM student s INNER JOIN 
(SELECT gradeid AS gid, MAX(birthday) AS minbir FROM student GROUP BY gradeid ) t
ON (s.birthday=t.minbir AND s.gradeid=t.gid )

-- 方法2 ：使用子查询

SELECT * FROM student WHERE (gradeid,birthday) IN (
SELECT gradeid , MAX(birthday)  FROM student 
GROUP BY gradeid  )

-- 查询名字首字符为刘 并含有飞的学生
SELECT * FROM student WHERE sname LIKE '王%武'

--  子查询内外关联？？？
SELECT * FROM house h
WHERE ( SELECT COUNT(*) FROM house WHERE sid = h.sid )>=2

-- 查询没有借阅信息的读者编号和读者姓名（使用not exists）(性能上的优势，针对大数据)
SELECT * FROM reader r WHERE  NOT EXISTS (
SELECT rid FROM borrow b WHERE r.rid = b.rid
)

-- 使用 IN

SELECT * FROM reader r WHERE r.rid NOT IN (
SELECT rid FROM borrow b )


-- 2017年9月11日

-- 向成绩表中添加3条记录 如果成绩大于100分则取消操作

--  begin;
INSERT INTO examresult VALUES ('14', '2017-09-02', '90.00', '3', '4');

COMMIT;

ROLLBACK;
 
SELECT * FROM examresult;



-- 建立视图，包含学生姓名，qq和年龄
DROP VIEW  IF EXISTS showinfo;

CREATE VIEW showinfo
AS
SELECT sname AS 姓名 ,qq AS QQ号, YEAR(NOW())-YEAR(birthday) AS 年龄 FROM student LIMIT 5;

SELECT * FROM showinfo WHERE 年龄>18;

-- 通过修改视图的数据（主键）可以修改主表的数据

-- 统计每个学生的课程的平均成绩

DROP VIEW IF EXISTS avgscore;

CREATE VIEW avgscore 
AS 
SELECT s.sname AS 姓名, AVG( e.score )AS 成绩, su.subjectname AS 课程名 
FROM student  s
LEFT JOIN examresult e ON s.stuno = e.st_id
LEFT JOIN `subject` su ON su.id = e.sb_id
WHERE s.stuno = e.st_id
GROUP BY e.st_id,e.sb_id

SELECT * FROM avgscore



-- 设计高分成绩单视图，只包含成绩在80分以上的学生信息，包括科目名称以及成绩

CREATE VIEW chengjibiao 
AS 
SELECT student.`sname` AS 姓名,su.subjectname AS 科目, examresult.`score` AS 成绩  
FROM examresult 
LEFT JOIN student ON student.`stuno` = examresult.`st_id`
LEFT JOIN `subject` su ON su.id = examresult.`sb_id`
WHERE examresult.score >80 

SELECT * FROM chengjibiao










